if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_create_schedule_ot')
          and type in ('P','PC'))
   drop procedure bd_proc_create_schedule_ot
go


create procedure bd_proc_create_schedule_ot (@userSerial varchar(20)) as
BEGIN
	--生成排班加班临时数据
		--说明：生成排班关联的可生产加班单的数据，用于之后生成加班单，
		--数据生成条件：1加班日期截止当前日期，2关联考勤表有打卡信息，4生成的数据与加班明细对比，不能存在时间重叠
		--步骤，1收集信息，2过滤不满足（最终要返回），3整理数据并保存到bd_temp_schedule_ot(保存之前，清除@userSerial的数据)

	DECLARE @nowDate date;--当前日期
	set @nowDate = DATEADD(day, -1, GETDATE())

	--操作人部门权限下的全部人员
	create table #users(
		user_serial bigint,
		user_work_calendar int
	)
	--排班明细临时表
	create table #scheduleDetails(
		id	bigint,
		user_serial	bigint,
		shift_id	int,
		shift_date	date,
		shift_name varchar(50),
		begin_time datetime,
		end_time datetime,
		ot_minute int
	)

	create table #returnData(
		user_serial bigint,
		shift_date date,
		error_msg varchar(max)
	)


	--1.收集信息
	--1.1收集操作人部门权限下的全部人员
	insert into #users
	select u.user_serial,u.user_work_calendar from dt_user u
		inner join
		(
		select dep.dep_no from dt_user u
		INNER JOIN WT_GLY_DEPS d on u.user_no = d.gly_no
		INNER JOIN dt_dep dep on dep.dep_serial = d.dep_serial
		where convert(varchar(20),u.user_serial) = @userSerial

	 ) a on u.dep_no like a.dep_no +'%'
	--过滤掉不允许申请加班的人员
	delete from #users where user_serial in (
		select x.user_serial  from (
			select u.user_serial,
						u.job_grade,
						u.technological_job,
						p.overtime_reject_administrative,
						p.overtime_reject_technology
			from dt_user u ,bd_sys_global_params p
			) x
		where x.overtime_reject_administrative like '%' + case when x.job_grade is null or x.job_grade='' then 'N/A' else x.job_grade end +'%' or
					x.overtime_reject_technology like  '%' + case when x.technological_job is null or x.technological_job='' then 'N/A' else x.technological_job end  +'%'
	)

	--1.2收集部门下全部人员在截止日期@nowDate，并有附加加班（is_have_ot=1）、而且未生成加班单（is_created_ot=0）的明细记录
	insert into #scheduleDetails
	select
		d.id,
		d.user_serial,
		d.shift_id,
		d.shift_date,
		bc.mc,
		DATEADD(mi, (case when (de.is_weekend = 0 and de.is_statutory = 0) then x.jbsbsj else x.zzSbsj end) , cast(d.shift_date as datetime)),
		DATEADD(mi, (case when (de.is_weekend = 0 and de.is_statutory = 0) then x.jbxbsj else x.zwXbsj end), cast(d.shift_date as datetime)),
		(case when (de.is_weekend = 0 and de.is_statutory = 0) then x.jbxbsj-x.jbsbsj else x.zwXbsj-x.zzSbsj end)
	from bd_kq_schedule_detail d
	INNER JOIN #users u on u.user_serial = d.user_serial
	left JOIN bd_kq_work_calendar_detail de on u.user_work_calendar = de.parent_id and d.shift_date = de.work_date
	left JOIN zt_banci bc on d.shift_id = bc.id and bc.is_delete=0
	left join (select
								a.parent,
								b.sbsj as jbsbsj,
								b.xbsj as jbxbsj,
							  case when b.sbsj is null then  a.sbsj else (case when a.sbsj<b.sbsj then a.sbsj else b.sbsj end) end as zzSbsj,
								case when b.xbsj is null then a.xbsj else (case when a.xbsj<b.xbsj then b.xbsj else a.xbsj end) end as zwXbsj
						from
								(
								select
										parent,
										case when sbsj<0 then -1440-sbsj else sbsj end as sbsj,
										case when xbsj<0 then -1440-xbsj else xbsj end as xbsj

								from zt_shid where  lx = 0 ) a
								left JOIN
								(
								select
										parent,
										case when sbsj<0 then -1440-sbsj else sbsj end as sbsj,
										case when xbsj<0 then -1440-xbsj else xbsj end as xbsj
								from zt_shid where lx = 1) b
						on a.parent = b.parent) x on bc.bh = x.parent
	where d.shift_date  <= @nowDate and isnull(d.is_have_ot,0)=1 and isnull(d.is_created_ot,0)=0

	--2.过滤不满足条件的数据
	--2.1过滤当日未打卡的排班明细
	insert into #returnData
	select s.user_serial,s.shift_date,'该日期未打卡，不能生成数据'  from #scheduleDetails s
	INNER JOIN bd_kq_timecard t on s.user_serial = t.user_serial and s.shift_date = t.card_date
	where t.swipe_first=9999 and t.swipe_last = 9999

	delete s from  #scheduleDetails s
	INNER JOIN #returnData r on s.user_serial = r.user_serial and s.shift_date = r.shift_date



	--2.2过滤加班区间与现有的加班有交集
	insert into #returnData
	select s.user_serial,s.shift_date,
	'该日期加班时段内已存在加班数据，不能生成数据,冲突时间段：' +
	case when x.kssj>s.begin_time then convert(varchar(16),x.kssj,120) else convert(varchar(16),s.begin_time,120) end +' - '+
				case when x.jssj<s.end_time then convert(varchar(16),x.jssj,120) else convert(varchar(16),s.end_time,120) end
	from #scheduleDetails s
	INNER JOIN (
		select j.user_serial,j.kssj,j.jssj,o.ot_date from lr_jiaba j
		INNER JOIN bd_over_time_order o on o.order_id = j.order_id
		where j.is_delete = 0 and o.is_delete = 0 and o.status !=2
		) x on x.user_serial = s.user_serial and x.ot_date = s.shift_date
	where  NOT ((s.end_time <=x.kssj) OR (s.begin_time >= x.jssj))

	delete s from  #scheduleDetails s
	INNER JOIN #returnData r on s.user_serial = r.user_serial and s.shift_date = r.shift_date

	--2.3过滤掉预计算实际加班时长不小于60分钟的数据
	insert into #returnData
	select x.user_serial,x.shift_date,'该日期预计算的实际加班时长小于1小时，不能生成数据'  from (
		select s.user_serial,s.shift_date,p.actual_hours
		from #scheduleDetails s
		left JOIN bd_over_time_preprosess p on s.user_serial = p.user_serial and s.shift_date = p.schedult_date) x
	where isnull(x.actual_hours,0)<60

	delete s from  #scheduleDetails s
	INNER JOIN #returnData r on s.user_serial = r.user_serial and s.shift_date = r.shift_date
	--3.整理数据并保存到bd_temp_schedule_ot(保存之前，清除@userSerial的数据)
	--3.1 删除旧数据
	delete from bd_temp_schedule_ot where creator_id = @userSerial

	--3.2插入数据
	insert into bd_temp_schedule_ot(user_serial,ot_date,shift_id,begin_time,end_time,ot_minute,creator_id,create_time)
	select user_serial,shift_date,shift_id,begin_time,end_time,ot_minute,@userSerial,getdate()
	from #scheduleDetails

	--4返回错误信息
	select
		u.user_no as userNo,
		u.user_lname as userName,
		d.dep_name as deptName,
		convert(varchar(10),r.shift_date,120) as otDate,
		r.error_msg as errorMsg
	from #returnData r
	INNER JOIN dt_user u on r.user_serial = u.user_serial
	INNER JOIN dt_dep d on u.user_dep = d.dep_serial

END
go
